﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using System.Data;

namespace NVR.DALSqlProvider
{
    public class DALDeviceInfoSqlProvider : IDAL.IDALDeviceInfo
    {
        #region IDALDeviceInfo 成员

        public int Insert(NVR.Entity.DeviceInfo obj)
        {
            //Database db = DatabaseFactory.CreateDatabase();
           // string strSql="Insert into "
           // var cmd = db.GetSqlStringCommand("");
            return 0;

        }

        public int Update(NVR.Entity.DeviceInfo obj)
        {
            throw new NotImplementedException();
        }

        public int Delete(NVR.Entity.DeviceInfo obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_DeviceInfo_delete") as SqlCommand;

            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@sn", obj.Sn);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public List<NVR.Entity.DeviceInfo> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetSqlStringCommand("select *from DeviceInfo_t") as SqlCommand;
            //3.通过db执行一个DBReader

            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List

            List<Entity.DeviceInfo> list = new List<Entity.DeviceInfo>();
            while (reader.Read())
            {
                NVR.Entity.DeviceInfo devInfo = new NVR.Entity.DeviceInfo();
                devInfo.DeviceID = reader["DeviceID"] is DBNull ? 0 : (Int32)reader["DeviceID"];
                devInfo.LocalIpv4 = reader["LocalIpv4"] is DBNull ? String.Empty : (String)reader["LocalIpv4"];
                devInfo.Port = reader["Port"] is DBNull ? String.Empty : (String)reader["Port"];
                devInfo.Mac = reader["Mac"] is DBNull ? String.Empty : (String)reader["Mac"];
                devInfo.Sn = reader["Sn"] is DBNull ? String.Empty : (String)reader["Sn"];
                devInfo.ServerIP = reader["ServerIP"] is DBNull ? String.Empty : (String)reader["ServerIP"];
                devInfo.ServerPort = reader["ServerPort"] is DBNull ? String.Empty : (String)reader["ServerPort"];
                devInfo.DeviceName = reader["DeviceName"] is DBNull ? String.Empty : (String)reader["DeviceName"];
                devInfo.DeviceDomain = reader["DeviceDomain"] is DBNull ? String.Empty : (String)reader["DeviceDomain"];
                devInfo.RegistTime = reader["RegistTime"] is DBNull ? DateTime.Now : (DateTime)reader["RegistTime"];
                devInfo.Remark = reader["Remark"] is DBNull ? String.Empty : (String)reader["Remark"];
                devInfo.DeviceStatus = reader["DeviceStatus"] is DBNull ? 0 : (Int32)reader["DeviceStatus"];
                devInfo.Handle = reader["Handle"] is DBNull ? String.Empty : (String)reader["Handle"];
                devInfo.ChanNum = reader["ChanNum"] is DBNull ? 0 : (Int32)reader["ChanNum"];
                devInfo.StartChan = reader["StartChan"] is DBNull ? 0 : (Int32)reader["StartChan"];
                devInfo.RemoteIPv4 = reader["RemoteIPv4"] is DBNull ? String.Empty : (String)reader["RemoteIPv4"];
                devInfo.RemotePort = reader["RemotePort"] is DBNull ? String.Empty : (String)reader["RemotePort"];
                devInfo.RegisterID = reader["RegisterID"] is DBNull ? 0 : (Int32)reader["RegisterID"];
                devInfo.IsCenterPreveiw = reader["IsCenterPreveiw"] is DBNull ? 0 : (Int32)reader["IsCenterPreveiw"];
                devInfo.STSServerIP = reader["STSServerIP"] is DBNull ? String.Empty : (String)reader["STSServerIP"];
                devInfo.STSServerPort = reader["STSServerPort"] is DBNull ? String.Empty : (String)reader["STSServerPort"];
                devInfo.STSServeIsUse = reader["STSServeIsUse"] is DBNull ? 0 : (Int32)reader["STSServeIsUse"];
                list.Add(devInfo);
            }
            reader.Close();
            return list;
        }

        public NVR.Entity.DeviceInfo LoadById(Guid DeviceInfoID)
        {
            throw new NotImplementedException();
        }
        #endregion
        #region IDALDeviceInfo 成员

        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="pageCurrent">当前页码，如第一页，二页</param>
        /// <param name="pageSize">每个页面记录的条数 如15条，20条，淘宝每页40条</param>
        /// <param name="counts">数据中查到的总记录数</param>
        /// <param name="pageNums">页数</param>
        /// <returns></returns>
        public List<NVR.Entity.DeviceInfo> PartPageFunc(int pageCurrent, int pageSize, out int counts, out int pageNums)
        {
            List<Entity.DeviceInfo> list = new List<Entity.DeviceInfo>();
            Database db = DatabaseFactory.CreateDatabase();

            #region  此处代码要注意思，对于存储过程要同时返回结果集和返回参数，要同时用ExecuteReader和ExecuteNonQuery
            /*var cmd = db.GetStoredProcCommand("outparamTest") as SqlCommand;
            //cmd.Parameters.AddWithValue("@a", "admin");
            SqlParameter para = new SqlParameter("@c", SqlDbType.Int, 4);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            SqlParameter para1 = new SqlParameter("@b", SqlDbType.Int, 4);
            para1.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para1);

            //cmd.Parameters.Add("@c", SqlDbType.Int).Direction = ParameterDirection.Output;
            //cmd.Parameters.Add("@b", SqlDbType.Int).Direction = ParameterDirection.Output;
            var reader=db.ExecuteReader(cmd);
            int x=db.ExecuteNonQuery(cmd);
            while (reader.Read())
           {
                NVR.Entity.DeviceInfo devInfo = new NVR.Entity.DeviceInfo();
                list.Add(devInfo);
            }*/
#endregion            
            var cmd = db.GetStoredProcCommand("proc_paged_part_select") as SqlCommand;
            cmd.Parameters.AddWithValue("@tblName", "DeviceInfo_t");
            cmd.Parameters.AddWithValue("@fldName", "Sn,RemoteIPv4,LocalIpv4,Mac,DeviceStatus,RegistTime");
            cmd.Parameters.AddWithValue("@pageSize", pageSize);
            cmd.Parameters.AddWithValue("@page", pageCurrent);
            cmd.Parameters.AddWithValue("@fldSort", "Sn"); //要排序的字段
            cmd.Parameters.AddWithValue("@Sort",0);  //0 升序，1：降序
            cmd.Parameters.AddWithValue("@strCondition", "");
            cmd.Parameters.AddWithValue("@ID", "Sn");
            cmd.Parameters.AddWithValue("@Dist", 0);
            cmd.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@Counts", SqlDbType.Int).Direction = ParameterDirection.Output;
            db.ExecuteNonQuery(cmd);
            counts = (int)cmd.Parameters["@Counts"].Value;
            pageNums = (int)cmd.Parameters["@pageCount"].Value;

            var reader=db.ExecuteReader(cmd);
            while (reader.Read())
            {
                NVR.Entity.DeviceInfo devInfo = new NVR.Entity.DeviceInfo();
                devInfo.RemoteIPv4 = reader["RemoteIPv4"] is DBNull ? String.Empty : (String)reader["RemoteIPv4"];
                devInfo.LocalIpv4 = reader["LocalIpv4"] is DBNull ? String.Empty : (String)reader["LocalIpv4"];
                devInfo.Mac = reader["Mac"] is DBNull ? String.Empty : (String)reader["Mac"];
                devInfo.Sn = reader["Sn"] is DBNull ? String.Empty : (String)reader["Sn"];
                devInfo.RegistTime = reader["RegistTime"] is DBNull ? DateTime.Now : (DateTime)reader["RegistTime"];
                devInfo.DeviceStatus = reader["DeviceStatus"] is DBNull ? 0 : (Int32)reader["DeviceStatus"];
                list.Add(devInfo);
            }
            return list;
        }
        #endregion

        #region IDALDeviceInfo 成员


        public List<NVR.Entity.DeviceInfo> LoadBySn(string sn)
        {
            {
                //1.通过database工厂得到当前数据库连接的对象  db
                Database db = DatabaseFactory.CreateDatabase();
                //2.通过db 获得一个sp构成的cmd
                var cmd = db.GetSqlStringCommand("select *from DeviceInfo_t where Sn='"+sn+"'") as SqlCommand;
                //3.通过db执行一个DBReader

                var reader = db.ExecuteReader(cmd);
                //4.遍历DBReader,生成新的Entity对象,然后添加到List

                List<Entity.DeviceInfo> list = new List<Entity.DeviceInfo>();
                while (reader.Read())
                {
                    NVR.Entity.DeviceInfo devInfo = new NVR.Entity.DeviceInfo();
                    devInfo.DeviceID = reader["DeviceID"] is DBNull ? 0 : (Int32)reader["DeviceID"];
                    devInfo.LocalIpv4 = reader["LocalIpv4"] is DBNull ? String.Empty : (String)reader["LocalIpv4"];
                    devInfo.Port = reader["Port"] is DBNull ? String.Empty : (String)reader["Port"];
                    devInfo.Mac = reader["Mac"] is DBNull ? String.Empty : (String)reader["Mac"];
                    devInfo.Sn = reader["Sn"] is DBNull ? String.Empty : (String)reader["Sn"];
                    devInfo.ServerIP = reader["ServerIP"] is DBNull ? String.Empty : (String)reader["ServerIP"];
                    devInfo.ServerPort = reader["ServerPort"] is DBNull ? String.Empty : (String)reader["ServerPort"];
                    devInfo.DeviceName = reader["DeviceName"] is DBNull ? String.Empty : (String)reader["DeviceName"];
                    devInfo.DeviceDomain = reader["DeviceDomain"] is DBNull ? String.Empty : (String)reader["DeviceDomain"];
                    devInfo.RegistTime = reader["RegistTime"] is DBNull ? DateTime.Now : (DateTime)reader["RegistTime"];
                    devInfo.Remark = reader["Remark"] is DBNull ? String.Empty : (String)reader["Remark"];
                    devInfo.DeviceStatus = reader["DeviceStatus"] is DBNull ? 0 : (Int32)reader["DeviceStatus"];
                    devInfo.Handle = reader["Handle"] is DBNull ? String.Empty : (String)reader["Handle"];
                    devInfo.ChanNum = reader["ChanNum"] is DBNull ? 0 : (Int32)reader["ChanNum"];
                    devInfo.StartChan = reader["StartChan"] is DBNull ? 0 : (Int32)reader["StartChan"];
                    devInfo.RemoteIPv4 = reader["RemoteIPv4"] is DBNull ? String.Empty : (String)reader["RemoteIPv4"];
                    devInfo.RemotePort = reader["RemotePort"] is DBNull ? String.Empty : (String)reader["RemotePort"];
                    devInfo.RegisterID = reader["RegisterID"] is DBNull ? 0 : (Int32)reader["RegisterID"];
                    devInfo.IsCenterPreveiw = reader["IsCenterPreveiw"] is DBNull ? 0 : (Int32)reader["IsCenterPreveiw"];
                    devInfo.STSServerIP = reader["STSServerIP"] is DBNull ? String.Empty : (String)reader["STSServerIP"];
                    devInfo.STSServerPort = reader["STSServerPort"] is DBNull ? String.Empty : (String)reader["STSServerPort"];
                    devInfo.STSServeIsUse = reader["STSServeIsUse"] is DBNull ? 0 : (Int32)reader["STSServeIsUse"];
                    list.Add(devInfo);
                }
                return list;
            }
        }
        #endregion

        #region IDALDeviceInfo 成员


        public List<Entity.DeviceTable> QueryListUserInfoFKUsername_DVRInfoFKSn_DeviceInfo(string sn)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_DeviceInfo_userInfo_NvrInfo") as SqlCommand;
            cmd.Parameters.AddWithValue("@sn", sn);
            var reader = db.ExecuteReader(cmd);
            List<Entity.DeviceTable> list = new List<Entity.DeviceTable>();
            while (reader.Read())
            {
                Entity.DeviceTable dt = new Entity.DeviceTable();
                dt.Sn = reader["Sn"] is DBNull ? String.Empty : (String)reader["Sn"];
                dt.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                dt.RemoteIPv4 = reader["RemoteIPv4"] is DBNull ? String.Empty : (String)reader["RemoteIPv4"];
                dt.LocalIpv4 = reader["LocalIpv4"] is DBNull ? String.Empty : (String)reader["LocalIpv4"];
                dt.Mac = reader["Mac"] is DBNull ? String.Empty : (String)reader["Mac"];
                dt.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                dt.DeviceStatus = (Int32)reader["DeviceStatus"] == 1 ? "在线" : "离线"; ;
                dt.RegistTime = reader["RegistTime"] is DBNull ? DateTime.Now : (DateTime)reader["RegistTime"];
                list.Add(dt);
            }
            return list;
        }
        #endregion
    }
}
